package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsBusiness;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsbusinessDao extends BaseJPADao<BmsBusiness,Integer> {

    List<BmsBusiness> findByIdIn(Set<Integer> ids);

    @Query(value = "select bb.id,bb.vendor_name vendorName,bb.addr_city addrCity,bb.addr_details addrDetails,bb.tags,bb.longitude,bb.latitude,bbtr.bms_type_id tagsId from bms_business bb " +
            "inner join bms_business_type_relation bbtr on bb.id = bbtr.bms_business_id where bb.vendor_name like %?1% " +
            "and IF(?2 != '',bb.addr_city=?2,1=1) and bb.is_unload = ?3 and bb.is_delete = ?4",nativeQuery = true)
    List<Map<String,Object>> findByVendorNameToMerchant(String vendorName, String addrCity,Integer isUnload,Integer isDelete, Pageable pageable);

    @Query(value = "select bb.id,bb.vendor_name vendorName,bb.addr_city addrCity,bb.addr_details addrDetails,bb.tags,bb.longitude,bb.latitude,bbtr.bms_type_id tagsId from bms_business bb " +
            "inner join bms_business_type_relation bbtr on bb.id = bbtr.bms_business_id where bb.vendor_name like %?1% and bbtr.bms_type_id in (?3) " +
            "and IF(?2 != '',bb.addr_city=?2,1=1) and bb.is_unload = ?4 and bb.is_delete = ?5",nativeQuery = true)
    List<Map<String,Object>> findByVendorNameToDank(String vendorName, String addrCity,Set typeIds,Integer isUnload,Integer isDelete, Pageable pageable);

    @Query(value = "select count(bb.id) num from bms_business bb " +
            "inner join bms_business_type_relation bbtr on bb.id = bbtr.bms_business_id where bb.vendor_name like %?1% and bbtr.bms_type_id in (?3) " +
            "and IF(?2 != '',bb.addr_city=?2,1=1) and bb.is_unload = ?4 and bb.is_delete = ?5",nativeQuery = true)
    Integer countByVendorNameToMerchant(String vendorName, String addrCity,Set typeIds,Integer isUnload,Integer isDelete);

    @Query(value = "select count(bb.id) num from bms_business bb " +
            "inner join bms_business_type_relation bbtr on bb.id = bbtr.bms_business_id where bb.vendor_name like %?1% " +
            "and IF(?2 != '',bb.addr_city=?2,1=1) and bb.is_unload = ?3 and bb.is_delete = ?4",nativeQuery = true)
    Integer countByVendorNameToMerchant(String vendorName, String addrCity,Integer isUnload,Integer isDelete);

    @Query(value = "select bb.id,bb.vendor_name,bb.addr_province,bb.addr_city,addr_district,bb.addr_details,bb.label,bb.latitude,bb.longitude from bms_business bb where bb.vendor_name like %?1% " +
            "and bb.is_delete = ?2 and bb.is_unload = ?3",nativeQuery = true)
    List<Map<String,Object>> findByVendorNameLikeAndIsDeleteAndIsUnload(String vendorName,Integer isDelete,Integer isUnload,Pageable pageable);

    @Query(value = "select bb.id,bb.vendor_name,bb.addr_city,bb.addr_details,bb.label,bb.latitude,bb.longitude from bms_business bb where bb.vendor_name like %?1% " +
            "and bb.is_delete = ?2 and bb.is_unload = ?3",nativeQuery = true)
    List<Map<String,Object>> findByVendorNameLikeAndIsDeleteAndIsUnload(String vendorName,Integer isDelete,Integer isUnload);

    List<BmsBusiness> findByVendorNameAndIsDeleteAndIsUnload(String vendorName,Integer isDelete,Integer isUnload);

    @Query(value="select bb.addr_city,count(bb.id) as num from bms_business bb where bb.id in (?1) " +
            "group by bb.addr_city",nativeQuery=true)
    List<Map<String,Object>> findByGroupByAddrCity(Set ids);

    @Query(value="select count(bb.id) as num from bms_business bb left join bms_business_type_relation bbtr on bb.id = bbtr.bms_business_id where bb.id in (?1) " +
            "and bbtr.bms_type_id = ?2",nativeQuery=true)
    List<Map<String,Object>> countUserDkInBusinessType(Set<Integer> ids,Integer businessTypeId);

    List<BmsBusiness> findByIsDeleteAndIsUnloadAndTagsLike(Integer isDelete,Integer isUnload,Pageable pageable,String tags);

    BmsBusiness findByIdAndIsDelete(Integer businessId,Integer isDelete);

    /**查找定位店铺**/
    @Query(value = "select bb.id,bb.latitude,bb.longitude from bms_business bb where bb.addr_city = ?1 and " +
            "bb.addr_district = ?2 and bb.id in (?3) and is_delete = 0 and is_unload = 0",nativeQuery = true)
    List<Map<String, Object>> findByLocation(String addrCity, String addrDistrict,Set businessId);

    /**查找店铺定位**/
    @Query(value="select bb.id,bb.latitude,bb.longitude from bms_business bb where IF(?1 != '',bb.addr_city=?1 and IF(?2 != '',bb.addr_district=?2,1=1),1=1) and bb.is_delete=?3 and bb.is_unload=?4",nativeQuery=true)
    List<Map<String, Object>> findByAddrCityToFrontShow(String addrCity, String addrDistrict, Integer isDelete, Integer isUnload);

    @Query(value = "select bb.* from bms_business bb left join bms_business_area_relation bbar on bb.id = " +
            "bbar.business_id where bb.is_delete = 0 and bb.is_auth = 1 and bb.is_unload = 0 and bbar.area_id in (?1)",nativeQuery = true)
    List<BmsBusiness> findBusinessByCircle(Set businessCircle);

    @Query(value = "SELECT *,(POWER(MOD(ABS(longitude - ?2),360),2) + POWER(ABS(latitude - ?3),2)) AS distance " +
            " FROM bms_business bb where vendor_name like %?1% and is_delete = 0 and is_unload = 0 " +
            "ORDER BY distance LIMIT ?4,?5",nativeQuery = true)
    List<BmsBusiness> getVendorNameOrderByDistance(String searchText, String longitude,
                                                   String latitude, Integer pageNum,Integer pageSize);

}
